import akshare as ak
import pandas as pd
import numpy as np
import sys,getopt
import openpyxl
import os

from datetime import date


def GetStockData(stock_file: str = "stock"):
    
    stock_df = pd.read_excel(stock_file + ".xlsx", sheet_name = 0 , converters={'代码':str})
    #print(stock_df)
    H_df = stock_df.loc[stock_df['代码'].str.len() == 5]
    A_df = stock_df.loc[stock_df['代码'].str.len() == 6]
    Cash_df = stock_df.loc[stock_df['代码'].str.len() == 7]
    fund_df = stock_df.loc[stock_df['代码'].str.len() == 8]
    
    H_df = H_df.dropna(axis=1,how='any')
    A_df = A_df.dropna(axis=1,how='any')
    Cash_df = Cash_df.dropna(axis=1,how='any')
    fund_df = fund_df.dropna(axis=1,how='any')
    
    #print(H_df)
    #print(A_df)
    print(Cash_df)
    
    # -----------------这个取的是深港通的， 如果是沪港通需要换个函数--------------------
    stock_sgt_settlement_exchange_rate_szse_df = ak.stock_sgt_settlement_exchange_rate_szse()
#    print(stock_sgt_settlement_exchange_rate_szse_df.iloc[-1])
    
    hkdcny_date = stock_sgt_settlement_exchange_rate_szse_df.iloc[-1]['适用日期']
    hkdcny_1 = stock_sgt_settlement_exchange_rate_szse_df.iloc[-1]['买入结算汇兑比率']
    hkdcny_2 = stock_sgt_settlement_exchange_rate_szse_df.iloc[-1]['卖出结算汇兑比率']
#    hkdcny = (hkdcny_1 + hkdcny_2 ) / 2.0
    hkdcny = hkdcny_1 
    print("深港通买入结算汇率:  " + '%f'%hkdcny + "    日期: " + hkdcny_date.strftime('%Y-%m-%d'))
    
    #----------------------计算A股持仓市值---------------------------------
    stock_zh_a_df = ak.stock_zh_a_spot_em()
    stock_zh_a_df = stock_zh_a_df[['代码','名称','最新价','总市值','年初至今涨跌幅']]
    stock_zh_a_df = stock_zh_a_df.loc[stock_zh_a_df['代码'].isin(A_df['代码'])]
    stock_zh_a_df['总市值'] = stock_zh_a_df['总市值'] / 1e8
    A_df = pd.merge(A_df,stock_zh_a_df, how="inner" , on="代码") 
    A_df['持仓市值'] = A_df['持仓'] * A_df['最新价'] / 1e4
    #print(A_df)

    #----------------------------基金， 目前只有 sh510310 ----------------------------------
#    fund_etf_spot_em_df = ak.fund_etf_spot_em()
    if not fund_df.empty:
         fund_etf_spot_em_df = ak.fund_etf_category_sina(symbol="ETF基金")
         fund_etf_spot_em_df = fund_etf_spot_em_df[['代码','名称','最新价']]
         fund_etf_spot_em_df = fund_etf_spot_em_df.loc[fund_etf_spot_em_df['代码'].isin(fund_df['代码'])]
         fund_etf_spot_em_df['总市值'] = 0 
         fund_etf_spot_em_df['年初至今涨跌幅'] = 0 

         fund_df = pd.merge(fund_df,fund_etf_spot_em_df, how="inner" , on="代码") 
         fund_df['持仓市值'] = fund_df['持仓'] * fund_df['最新价'] / 1e4

         print(fund_df)
         
    #----------------------计算港股持仓市值---------------------------------
    stock_hk_a_df = ak.stock_hk_spot_em()
    stock_hk_a_df = stock_hk_a_df[['代码','名称','最新价']]
    stock_hk_a_df = stock_hk_a_df.loc[stock_hk_a_df['代码'].isin(H_df['代码'])]
    H_df = pd.merge(H_df, stock_hk_a_df, how="inner" , on="代码")
    
    H_df['总市值'] = H_df['最新价'] * H_df['港股总股本']
    del H_df['港股总股本']
    
    H_df['年初至今涨跌幅'] = ( H_df['最新价'] / H_df['港股去年收盘价'] - 1 ) * 100
    del H_df['港股去年收盘价']
    
    
    H_df['持仓市值港币'] = H_df['持仓'] * H_df['最新价'] / 1e4
    H_df['持仓市值'] = H_df['持仓市值港币'] * hkdcny 
    
    #print(H_df)
    
    #---------------------------------------------------
    T_df = pd.concat([A_df,H_df], axis=0)

    if not fund_df.empty:
        T_df = pd.concat([T_df,fund_df], axis=0)

    T_df = T_df.sort_values(by='序号')
    T_df.set_index('序号',inplace=True, drop=True)
    T_df.index.name = None
    
    total_sum = T_df['持仓市值'].sum()
    meituan = T_df.loc[T_df.代码 == '03690','持仓市值']            # 没有美团直接给0
    Cash = ( Cash_df.iloc[0]['现值'] + Cash_df.iloc[1]['现值'] ) / 1e4
    net_asset = total_sum + Cash
    inc = net_asset * 100 / ( Cash_df.iloc[2]['现值'] / 1e4) - 100
    inc_value = net_asset - ( Cash_df.iloc[2]['现值'] / 1e4) 

    T_df['持股比例'] = T_df['持仓市值'] / net_asset 
    T_df['持股比例'] = T_df['持股比例'].apply(lambda x: format(x, '.2%'))  
    T_df['年内涨幅'] = T_df['年初至今涨跌幅'].apply(lambda x: format(x/100, '.2%'))
    T_df.rename(columns = {'总市值':'当前市值'}, inplace = True) 
    
    print("总持仓(含融资不含美团):   " + '%.2f'%(total_sum - meituan))
    print("净资产(含美团)合计:   " + '%.2f'%net_asset)
    print("券商显示(不含美团):   " + '%.2f'%(net_asset - meituan))

    print("年内增长值:  " + '%.2f'%inc_value)
    print("年内增长率:  " + '%.2f'%inc + "%")
    
    
    
    del T_df['持仓']                # 可以删除这行保留持仓数量
    del T_df['持仓市值']            # 可以删除这行保留持仓市值
    del T_df['持仓市值港币']
    del T_df['年初至今涨跌幅']
    
    T_df['当前市值'] = T_df['当前市值'].apply(lambda x: format(x, '.2f'))  
    #---------------------- 添加额外的数据 --------------
    inc_data = [{'代码' : '8000001' ,'名称' : '账户年内增长' ,'年内涨幅' : '%.2f'%inc + "%"}]
    inc_df = pd.DataFrame(inc_data) 
    T_df = pd.concat([T_df,inc_df], axis=0)

    hkd_data = [{'代码' : hkdcny_date.strftime('%Y-%m-%d') ,'名称' : '港币汇率' ,'港币汇率' : hkdcny}]
    hkd_df = pd.DataFrame(hkd_data) 
    T_df = pd.concat([T_df,hkd_df], axis=0)

    mv_data = [{'代码' : '8000002' ,'名称' : '账户净资产' ,'当前市值' : net_asset}]
    mv_df = pd.DataFrame(mv_data) 
    T_df = pd.concat([T_df,mv_df], axis=0)

#    T_df = T_df.round(2)
#    T_df['当前市值'] = T_df['当前市值'].apply(lambda x: format(x, '.2f'))  
    print(T_df)
    #----------------  输出 --------------------------
    today = date.today()
    filename = today.strftime('%Y-%m-%d') +  ".xlsx"
    print(filename)

    if os.path.exists(filename):
        book = openpyxl.load_workbook(filename)
    else:
        book = openpyxl.Workbook()

    writer = pd.ExcelWriter(filename,engine='openpyxl')
    writer.book = book
    
    T_df.to_excel(writer, sheet_name=stock_file, index=False)
    writer.save()
    writer.close()
    

if __name__ == '__main__':
    GetStockData(stock_file = sys.argv[1])
